package tms.sync.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.*;

import tms.sync.dao.TmsSyncEXclassDao;
import tms.sync.service.TmsSyncCommonService;


public class TmsSyncEXclassDaoImpl implements TmsSyncEXclassDao{
	
	private static TmsSyncEXclassDaoImpl tmsSyncEXclassDaoImpl;
	
	public static TmsSyncEXclassDaoImpl getInstance(){
		if(tmsSyncEXclassDaoImpl == null){
			tmsSyncEXclassDaoImpl = new TmsSyncEXclassDaoImpl();
		}
		return tmsSyncEXclassDaoImpl;
	}
	
	public TmsSyncEXclassDaoImpl() {
		init();
	}

	private void init(){
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("[ LOG ] : DRIVER LOADING SUCCESS" + e);
		}
	}
	
	private Connection getConnection() throws SQLException{
		Connection conn = null;
		String url = "jdbc:oracle:thin:@134.75.122.30:1521/hoseo";
		String id = "chulsuk";
		String psw = "ghtjcnftjr)1";
		conn = DriverManager.getConnection(url, id, psw);
		return conn;
	}
	
	private void close(Connection conn, Statement stmt, ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
			}
		}
		if(stmt != null){
			try {
				stmt.close();
			} catch (SQLException e) {
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
	}

	@Override
	public List<?> getUserInfo(String strUserId) {
		List listResult = new ArrayList();
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String sql = "SELECT USER_ID, USER_NM FROM CMN_USE_USER";
		
		try {
			conn = getConnection();
			System.out.println("[ LOG ] : DB CONNECTION SUCCESS");
			
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, strUserId);
			
			rs = psmt.executeQuery();
			System.out.println("[ LOG ] : SQL START");
			
			while(rs.next()){
				int i = 1;
				Map<String, Object> mapResult = new HashMap<String, Object>();
				mapResult.put("USER_ID", rs.getString(i++));
				mapResult.put("USER_NM", rs.getString(i++));
				
				listResult.add(mapResult);
			}
			System.out.println("[ LOG ] : SQL RESULT LIST = "+listResult);
			
		} catch (SQLException e) {
			System.out.println("[ LOG ] : DB CONNECTION FAIL : "+e);
		}finally{
			close(conn, psmt, rs);
			System.out.println("[ LOG ] : END");
		}
		return listResult;
	}
	
	// 부서코드 max 날짜 | 용어사전 PK맥스 번호 가져오기
	@Override
	public Map getDeptDictionaryInfo() {
		Connection conn = null;
		Connection conn2 = null;
		PreparedStatement psmt = null;
		PreparedStatement psmt2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String sql = "select MAX(REPLACE(DIC_EST, 'HS_DEPT_', '')) AS MAX_CNT " +
				"FROM CMN_DICTIONARY " +
				"WHERE DIC_EST LIKE 'HS_DEPT_%'";
		String sql2 = "SELECT TO_CHAR((CASE WHEN MAX(CRT_DTHR) >= MAX(UPD_DTHR) THEN MAX(CRT_DTHR) ELSE MAX(UPD_DTHR) END), 'YYYYMMDD') AS MAX_DATE FROM CMN_DEPARTMENT";
		
		Map<String, Object> mapResult = new HashMap<String, Object>();
		
		try {
			conn = getConnection();
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			
			while(rs.next()){
				String voMaxCnt = rs.getString(1);
				if(voMaxCnt == null || voMaxCnt.equals("")){
					mapResult.put("MAX_CNT", "1");
				}else{
					mapResult.put("MAX_CNT", voMaxCnt);
				}
			}
		} catch (SQLException e) {
			System.out.println("[ LOG ] : DB CONNECTION FAIL : "+e);
		}finally{
			close(conn, psmt, rs);
			System.out.println("[ LOG ] : END DEPT SEARCH PARAM");
		}
		
		try {
			conn2 = getConnection();
			psmt2 = conn2.prepareStatement(sql2);
			rs2 = psmt2.executeQuery();
			
			while(rs2.next()){
				String voMaxDate = rs2.getString(1);
				if(voMaxDate == null || voMaxDate.equals("")){
					mapResult.put("MAX_DATE", "19000101");
				}else{
					mapResult.put("MAX_DATE", voMaxDate);
				}
			}
		} catch (SQLException e) {
			System.out.println("[ LOG ] : DB CONNECTION FAIL : "+e);
		}finally{
			close(conn2, psmt2, rs2);
			System.out.println("[ LOG ] : END DEPT SEARCH PARAM");
		}
		
		return mapResult;
	}
	
	// 용어 사전 insert / dept insert
	@Override
	public int getDictionaryInsert(Map strInsertData) {
		TmsSyncCommonService tmsSyncCommonService = new TmsSyncCommonService();
		tmsSyncCommonService.setDefInsertData(strInsertData);
		
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String sql = "INSERT INTO CMN_DICTIONARY(DIC_EST, LANG_CD, DIC_WORD, USE_YN, CRT_USER_ID, CRT_DTHR, CRT_PGM_ID, CRT_IP_MAC)" +
				" VALUES(?, 'KOR', ?, 'Y', ?, SYSDATE, ?, ?)";
		int count = 0;
		int i = 1;
		
		try {
			conn = getConnection();
			System.out.println("[ LOG ] : DB CONNECTION SUCCESS - KOR DICTIONARY INSERT");
			
			psmt = conn.prepareStatement(sql);
			
			psmt.setString(i++, strInsertData.get("DIC_EST").toString());
			psmt.setString(i++, strInsertData.get("DEPT_NM").toString());
			psmt.setString(i++, strInsertData.get("CRT_USER_ID").toString());
			psmt.setString(i++, strInsertData.get("CRT_PGM_ID").toString());
			psmt.setString(i++, strInsertData.get("CRT_IP_MAC").toString());
			
			count = psmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("[ LOG ] : DB CONNECTION FAIL : "+e);
		}finally{
			close(conn, psmt, rs);
			System.out.println("[ LOG ] : END");
		}
		
		
		Connection conn2 = null;
		PreparedStatement psmt2 = null;
		ResultSet rs2 = null;
		
		String sql2 = "INSERT INTO CMN_DICTIONARY(DIC_EST, LANG_CD, DIC_WORD, USE_YN, CRT_USER_ID, CRT_DTHR, CRT_PGM_ID, CRT_IP_MAC) VALUES(?, 'ENG', ?, 'Y', ?, SYSDATE, ?, ?)";
		int count2 = 0;
		int j = 1;
		
		try {
			conn2 = getConnection();
			System.out.println("[ LOG ] : DB CONNECTION SUCCESS - ENG DICTIONARY INSERT");
			
			psmt2 = conn2.prepareStatement(sql2);
			
			psmt2.setString(j++, strInsertData.get("DIC_EST").toString());
			psmt2.setString(j++, strInsertData.get("DEPT_NM").toString());
			psmt2.setString(j++, strInsertData.get("CRT_USER_ID").toString());
			psmt2.setString(j++, strInsertData.get("CRT_PGM_ID").toString());
			psmt2.setString(j++, strInsertData.get("CRT_IP_MAC").toString());
			
			count2 = psmt2.executeUpdate();
		} catch (SQLException e) {
			System.out.println("[ LOG ] : DB CONNECTION FAIL : "+e);
		}finally{
			close(conn2, psmt2, rs2);
			System.out.println("[ LOG ] : END");
		}
		
		return count+count2;
	}
	
	// dept insert 부서코드 insert
	@Override
	public int getDeptInsert(Map strInsertData) {
		TmsSyncCommonService tmsSyncCommonService = new TmsSyncCommonService();
		
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String sql = "INSERT INTO CMN_DEPARTMENT(DEPT_CD, DEPT_NM, UPPER_DEPT_CD, DEPT_DIV_CD, SHOW_YN, DEPT_ENG_NM, SHORT_NM, PRT_ORD, DIC_EST, OPEN_DT, CLOSE_DT, CRT_USER_ID, CRT_DTHR, CRT_PGM_ID, CRT_IP_MAC)" +
				" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, ?, ?)";
		int count = 0;
		int i = 1;
		
		try {
			conn = getConnection();
			System.out.println("[ LOG ] : DB CONNECTION SUCCESS - DEPT INSERT");
			
			psmt = conn.prepareStatement(sql);
			
//			psmt.setString(i++, strInsertData.get("DEPT_CD").toString());
//			psmt.setString(i++, strInsertData.get("DEPT_NM").toString());
//			psmt.setString(i++, strInsertData.get("UP_CD").toString());
//			psmt.setString(i++, strInsertData.get("DEPT_DIV").toString());
//			psmt.setString(i++, strInsertData.get("USE_YN").toString());
//			psmt.setString(i++, strInsertData.get("DEPT_NM_ENG").toString());
			
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "DEPT_CD"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "DEPT_NM"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "UP_CD"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "DEPT_DIV"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "USE_YN"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "DEPT_NM_ENG"));
			
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "ALIAS"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "PRT_ORD"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "DIC_EST"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheckOpenDt(strInsertData, "CRE_DT"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "ABO_DT"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "CRT_USER_ID"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "CRT_PGM_ID"));
			psmt.setString(i++, tmsSyncCommonService.setIfNullCheck(strInsertData, "CRT_IP"));
			
//			psmt.setString(i++, strInsertData.get("ALIAS").toString());
//			psmt.setString(i++, strInsertData.get("PRT_ORD").toString());
//			psmt.setString(i++, strInsertData.get("DIC_EST").toString());
//			psmt.setString(i++, strInsertData.get("CRE_DT").toString());
//			psmt.setString(i++, strInsertData.get("ABO_DT").toString());
//			psmt.setString(i++, strInsertData.get("CRT_USER_ID").toString());
//			psmt.setString(i++, strInsertData.get("CRT_PGM_ID").toString());
//			psmt.setString(i++, strInsertData.get("CRT_IP").toString());
			
			count = psmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("[ LOG ] : DB CONNECTION FAIL : "+e);
		}finally{
			close(conn, psmt, rs);
			System.out.println("[ LOG ] : END");
		}
		return count;
	}
	
}

